#Boa:Dialog:LArtSucursal

import wx
import consultasBD

def create(parent):
    return LArtSucursal(parent)

[wxID_LARTSUCURSAL, wxID_LARTSUCURSALBTN_LISTAR, wxID_LARTSUCURSALBUTTON1, 
 wxID_LARTSUCURSALCHECKBOX1, wxID_LARTSUCURSALCODIGO_SUSSAN, 
 wxID_LARTSUCURSALCOMBOCOLOR, wxID_LARTSUCURSALCOMBOGRUPO, 
 wxID_LARTSUCURSALCOMBOTALLE, wxID_LARTSUCURSALDESCRIPCION, 
 wxID_LARTSUCURSALESTACION, wxID_LARTSUCURSALINNOMBREPROVEEDOR, 
 wxID_LARTSUCURSALINPROVEEDOR, wxID_LARTSUCURSALPTC, 
 wxID_LARTSUCURSALSTATICLINE1, wxID_LARTSUCURSALSTATICTEXT1, 
 wxID_LARTSUCURSALSTATICTEXT10, wxID_LARTSUCURSALSTATICTEXT11, 
 wxID_LARTSUCURSALSTATICTEXT12, wxID_LARTSUCURSALSTATICTEXT2, 
 wxID_LARTSUCURSALSTATICTEXT3, wxID_LARTSUCURSALSTATICTEXT4, 
 wxID_LARTSUCURSALSTATICTEXT5, wxID_LARTSUCURSALSTATICTEXT6, 
 wxID_LARTSUCURSALSTATICTEXT7, wxID_LARTSUCURSALSTATICTEXT8, 
 wxID_LARTSUCURSALSTATICTEXT9, 
] = [wx.NewId() for _init_ctrls in range(26)]

class LArtSucursal(wx.Dialog):
    def _init_ctrls(self, prnt):
        # generated method, don't edit
        wx.Dialog.__init__(self, id=wxID_LARTSUCURSAL, name=u'LArtSucursal',
              parent=prnt, pos=wx.Point(436, 166), size=wx.Size(443, 278),
              style=wx.DEFAULT_DIALOG_STYLE, title=u'Listado de articulos')
        self.SetClientSize(wx.Size(435, 244))

        self.staticText7 = wx.StaticText(id=wxID_LARTSUCURSALSTATICTEXT7,
              label=u'Listado de articulos', name='staticText7', parent=self,
              pos=wx.Point(12, 3), size=wx.Size(124, 15), style=0)

        self.staticLine1 = wx.StaticLine(id=wxID_LARTSUCURSALSTATICLINE1,
              name='staticLine1', parent=self, pos=wx.Point(12, 19),
              size=wx.Size(420, 2), style=0)

        self.staticText9 = wx.StaticText(id=wxID_LARTSUCURSALSTATICTEXT9,
              label='P.T.C.', name='staticText9', parent=self, pos=wx.Point(12,
              27), size=wx.Size(31, 13), style=0)

        self.staticText1 = wx.StaticText(id=wxID_LARTSUCURSALSTATICTEXT1,
              label='Cod. Sussan', name='staticText1', parent=self,
              pos=wx.Point(220, 27), size=wx.Size(61, 13), style=0)

        self.staticText3 = wx.StaticText(id=wxID_LARTSUCURSALSTATICTEXT3,
              label=u'Descripcion', name='staticText3', parent=self,
              pos=wx.Point(12, 59), size=wx.Size(60, 15), style=0)

        self.staticText5 = wx.StaticText(id=wxID_LARTSUCURSALSTATICTEXT5,
              label=u'Talle', name='staticText5', parent=self, pos=wx.Point(12,
              91), size=wx.Size(28, 15), style=0)

        self.staticText4 = wx.StaticText(id=wxID_LARTSUCURSALSTATICTEXT4,
              label=u'Estacion', name='staticText4', parent=self,
              pos=wx.Point(220, 91), size=wx.Size(55, 15), style=0)

        self.staticText8 = wx.StaticText(id=wxID_LARTSUCURSALSTATICTEXT8,
              label=u'Color', name=u'staticText8', parent=self, pos=wx.Point(12,
              124), size=wx.Size(33, 15), style=0)

        self.staticText6 = wx.StaticText(id=wxID_LARTSUCURSALSTATICTEXT6,
              label=u'Grupo', name='staticText6', parent=self, pos=wx.Point(220,
              124), size=wx.Size(39, 15), style=0)

        self.staticText2 = wx.StaticText(id=wxID_LARTSUCURSALSTATICTEXT2,
              label='Codigo', name='staticText2', parent=self, pos=wx.Point(12,
              160), size=wx.Size(33, 13), style=0)

        self.staticText12 = wx.StaticText(id=wxID_LARTSUCURSALSTATICTEXT12,
              label='Proveedor', name='staticText12', parent=self,
              pos=wx.Point(12, 176), size=wx.Size(50, 13), style=0)

        self.staticText10 = wx.StaticText(id=wxID_LARTSUCURSALSTATICTEXT10,
              label='Nombre', name='staticText10', parent=self,
              pos=wx.Point(220, 160), size=wx.Size(37, 13), style=0)

        self.staticText11 = wx.StaticText(id=wxID_LARTSUCURSALSTATICTEXT11,
              label='Proveedor', name='staticText11', parent=self,
              pos=wx.Point(220, 176), size=wx.Size(50, 13), style=0)

        self.ptc = wx.TextCtrl(id=wxID_LARTSUCURSALPTC, name='ptc', parent=self,
              pos=wx.Point(80, 27), size=wx.Size(120, 21), style=0, value='')

        self.codigo_sussan = wx.TextCtrl(id=wxID_LARTSUCURSALCODIGO_SUSSAN,
              name='codigo_sussan', parent=self, pos=wx.Point(288, 27),
              size=wx.Size(130, 21), style=0, value=u'')

        self.descripcion = wx.TextCtrl(id=wxID_LARTSUCURSALDESCRIPCION,
              name=u'descripcion', parent=self, pos=wx.Point(80, 59),
              size=wx.Size(336, 21), style=0, value=u'')

        self.comboTalle = wx.ComboBox(choices=[],
              id=wxID_LARTSUCURSALCOMBOTALLE, name='comboTalle', parent=self,
              pos=wx.Point(80, 88), size=wx.Size(130, 21), style=0, value='')
        self.comboTalle.SetLabel('')

        self.estacion = wx.ComboBox(choices=["", "Verano", "Primavera", "Otono",
              "Invierno"], id=wxID_LARTSUCURSALESTACION, name='estacion',
              parent=self, pos=wx.Point(288, 88), size=wx.Size(130, 21),
              style=0, value='')
        self.estacion.SetLabel('')

        self.comboColor = wx.ComboBox(choices=[],
              id=wxID_LARTSUCURSALCOMBOCOLOR, name='comboColor', parent=self,
              pos=wx.Point(80, 120), size=wx.Size(130, 21), style=0, value='')
        self.comboColor.SetLabel('')

        self.comboGrupo = wx.ComboBox(choices=[],
              id=wxID_LARTSUCURSALCOMBOGRUPO, name='comboGrupo', parent=self,
              pos=wx.Point(288, 120), size=wx.Size(130, 21), style=0, value='')
        self.comboGrupo.SetLabel('')

        self.inProveedor = wx.TextCtrl(id=wxID_LARTSUCURSALINPROVEEDOR,
              name='inProveedor', parent=self, pos=wx.Point(80, 160),
              size=wx.Size(128, 21), style=0, value='')

        self.inNombreProveedor = wx.TextCtrl(id=wxID_LARTSUCURSALINNOMBREPROVEEDOR,
              name='inNombreProveedor', parent=self, pos=wx.Point(288, 160),
              size=wx.Size(128, 21), style=0, value='')

        self.checkBox1 = wx.CheckBox(id=wxID_LARTSUCURSALCHECKBOX1,
              label='Incluir articulos con stock 0 (cero)', name='checkBox1',
              parent=self, pos=wx.Point(12, 208), size=wx.Size(184, 13),
              style=0)
        self.checkBox1.SetValue(False)
        self.checkBox1.Bind(wx.EVT_CHECKBOX, self.OnIncluirArticulosCeros,
              id=wxID_LARTSUCURSALCHECKBOX1)

        self.button1 = wx.Button(id=wxID_LARTSUCURSALBUTTON1, label='Salir',
              name='button1', parent=self, pos=wx.Point(326, 210),
              size=wx.Size(85, 27), style=0)
        self.button1.Bind(wx.EVT_BUTTON, self.OnButtonSalir,
              id=wxID_LARTSUCURSALBUTTON1)

        self.btn_listar = wx.Button(id=wxID_LARTSUCURSALBTN_LISTAR,
              label=u'Listar', name=u'btn_listar', parent=self,
              pos=wx.Point(222, 210), size=wx.Size(85, 27), style=0)
        self.btn_listar.Bind(wx.EVT_BUTTON, self.OnBtn_listarButton,
              id=wxID_LARTSUCURSALBTN_LISTAR)

    def __init__(self, parent):
        self._init_ctrls(parent)
        self.padrepadre= parent
        self.sucursalNro = parent.sucursalNro
        self.llenaCombos()
        self.incluirCeros = "AND (c.stock <> '0' OR d.stock <> '0' OR e.stock <> '0' OR f.stock<>'0') "

    def llenaCombos(self):
        #GRUPOS
        list = []
        condicionSQL = "1 ORDER BY nombregrupo"
        resultados = consultasBD.executaSQL(self,"*","grupos",condicionSQL)
        for r in resultados:
                list.append(str(r[1])+" / "+str(r[0]))
        self.comboGrupo.SetItems(list)
        
        #COLORES
        list = []
        condicionSQL = "1 ORDER BY nombre"
        resultados = consultasBD.executaSQL(self,"*","colores",condicionSQL)
        for r in resultados:
                list.append(str(r[1])+" / "+str(r[0]))
        self.comboColor.SetItems(list)
        
        #Talles
        list = []
        condicionSQL = "1 "
        resultados = consultasBD.executaSQL(self,"DISTINCT(talle)","producto_talle_color ",condicionSQL)
        for r in resultados:
                list.append(str(r[0]))
        self.comboTalle.SetItems(list)

    def consultaSQL(self):
        #consulta por PTC
        if str(self.ptc.Value) != "":
            r01 = "SELECT ptc,codsussan,talle,colores.nombre,descripcion,proveedor,precioventa,grupo,sucursal,stock "
            r02 = "FROM colores, "
            r03 = "(SELECT paso4.ptc,codsussan,talle,color,descripcion,nombre as proveedor,preciolt1 as precioventa,nombregrupo as grupo,sucursal,stock "
            r04 = " FROM stock, "
            r05 = " (SELECT ptc,codsussan,talle,color,descripcion,codprov,nombre,preciolt1,grupo,nombregrupo "
            r06 = "  FROM proveedor, "
            r07 = "  (SELECT id as ptc, codsussan, talle, color, descri as descripcion, proveedor as codprov, preciolt1, paso2.grupo, nombregrupo "
            r08 = "   FROM grupos, "
            r09 = "   (SELECT paso1.codsussan,proveedor,descri,preciolt1,grupo,paso1.id,paso1.talle,paso1.color "
            r10 = "    FROM productos, "
            r11 = "    (SELECT id,codsussan,talle,color "
            r12 = "     FROM producto_talle_color "
            r13 = "     WHERE id='"+self.ptc.GetValue()+"') as paso1 "
            r14 = "    WHERE productos.codsussan = paso1.codsussan) as paso2 "
            r15 = "   WHERE paso2.grupo = grupos.idgrupo) as paso3 "
            r16 = "  WHERE proveedor.idproveedor = paso3.codprov) as paso4 "
            r17 = " WHERE stock.ptc = paso4.ptc) as paso5 "
            r18 = "WHERE paso5.color = colores.idcolor"
            sql = r01+r02+r03+r04+r05+r06+r07+r08+r09+r10+r11+r12+r13+r14+r15+r16+r17+r18
            return consultasBD.ordenCruda(SQL)
        #consulta por CODSUSSAN
        if str(self.codigo_sussan.GetValue()) != "":
            r01 = "SELECT ptc,codsussan,talle,colores.nombre,descripcion,proveedor,precioventa,grupo,sucursal,stock "
            r02 = "FROM colores, "
            r03 = "(SELECT paso4.ptc,codsussan,talle,color,descripcion,nombre as proveedor,preciolt1 as precioventa,nombregrupo as grupo,sucursal,stock "
            r04 = " FROM stock, "
            r05 = " (SELECT ptc,codsussan,talle,color,descripcion,codprov,nombre,preciolt1,grupo,nombregrupo "
            r06 = "  FROM proveedor, "
            r07 = "  (SELECT id as ptc, codsussan, talle, color, descri as descripcion, proveedor as codprov, preciolt1, paso2.grupo, nombregrupo "
            r08 = "   FROM grupos, "
            r09 = "   (SELECT paso1.codsussan,proveedor,descri,preciolt1,grupo,paso1.id,paso1.talle,paso1.color "
            r10 = "    FROM productos, "
            r11 = "    (SELECT id,codsussan,talle,color "
            r12 = "     FROM producto_talle_color "
            r13 = "     WHERE codsussan='"+str(self.codigo_sussan.GetValue())+"') as paso1 "
            r14 = "    WHERE productos.codsussan = paso1.codsussan) as paso2 "
            r15 = "   WHERE paso2.grupo = grupos.idgrupo) as paso3 "
            r16 = "  WHERE proveedor.idproveedor = paso3.codprov) as paso4 "
            r17 = " WHERE stock.ptc = paso4.ptc) as paso5 "
            r18 = "WHERE paso5.color = colores.idcolor"
            sql = r01+r02+r03+r04+r05+r06+r07+r08+r09+r10+r11+r12+r13+r14+r15+r16+r17+r18
            return consultasBD.ordenCruda(SQL)
        #otras consultas

    def OnBtn_listarButton(self, event):
        import Vista
        import time
        
        ptc = "AND a.id= '%s'" % str(self.ptc.Value)
        if str(self.ptc.Value) == "":
            ptc = ""
            
        provee = "AND b.proveedor = '%s'" % str(self.inProveedor.Value)
        if str(self.inProveedor.Value) == "":
            provee = ""
        
        estacion = self.estacion.Value 
        if estacion=="":
            estacion="%%"

        if self.comboGrupo.Value<>"":
            grupo = self.comboGrupo.Value.split(" / ")[1]
        else:
            grupo = "%%"

        if self.comboColor.Value<>"":
            color = self.comboColor.Value.split(" / ")[1]
        else:
            color = "%%"            
        
        codsussan = "AND a.codsussan like'%%%s%%' " % str(self.codigo_sussan.GetValue())

        sentenciaSQL = []
        sentenciaSQL.append("SELECT distinct a.id,a.codsussan,a.talle,CONCAT(a.color,' / ',col.nombre),b.descri, \n")
        sentenciaSQL.append(       "CONCAT(p.idproveedor),b.preciolt1,b.grupo, \n")
        sentenciaSQL.append(       "COALESCE(c.stock, 0) as SANTAFE, COALESCE(d.stock, 0) as PARANA ,COALESCE(e.stock, 0) as SASKA ,COALESCE(f.stock, 0) as SPEATONAL \n")
        sentenciaSQL.append("FROM colores col , producto_talle_color a JOIN productos b ON (a.codsussan=b.codsussan) \n")
        sentenciaSQL.append(     "JOIN proveedor p ON (b.proveedor=p.idproveedor) \n")
        sentenciaSQL.append(     "LEFT JOIN stock c ON (a.id=c.ptc AND c.sucursal = 1) \n")
        sentenciaSQL.append(     "LEFT JOIN stock d ON (a.id=d.ptc AND d.sucursal = 2) \n")
        sentenciaSQL.append(     "LEFT JOIN stock e ON (a.id=e.ptc AND e.sucursal = 3) \n")
        sentenciaSQL.append(     "LEFT JOIN stock f ON (a.id=f.ptc AND f.sucursal = 4) \n")
        sentenciaSQL.append("WHERE col.idcolor=a.color and (a.talle LIKE '%%%s%%') \n")
        sentenciaSQL.append(     "AND (a.color LIKE '%%%s%%') \n")
        sentenciaSQL.append(     "AND (b.descri like '%%%s%%') %s \n")
        sentenciaSQL.append(     "AND (p.nombre like '%%%s%%') \n")
        sentenciaSQL.append(     "AND b.estacion like '%s' \n")
        sentenciaSQL.append(     "AND b.grupo LIKE '%s' %s %s \n")
        sentenciaSQL.append(self.incluirCeros)

        aux = ""
        for item in sentenciaSQL:
            aux += item
            
        SQL = aux % (str(self.comboTalle.Value),str(color),str(self.descripcion.Value), \
                     str(provee),str(self.inNombreProveedor.Value),str(estacion),str(grupo),ptc,codsussan)
        print SQL
        
        print "Procesando la consulta"
        tiempoInicio = time.time()
        datos = consultasBD.ordenCruda(SQL)
        tiempoFinal = time.time()
        print "Proceso finalizado"
        print "Tiempo de ejecucion: ", tiempoFinal - tiempoInicio

        #levantar de la base de datos
        columnas = ['PTC', 'Codigo\nSussan', 'Talle','Color ','Descrip.','Proveedor','P.Venta','Grupo','Stock\nSta Fe','Stock\nParana','Stock\nSaska','Stock\nPeatonal']
        #alineacion 0 => izquierda
        #alineacion 1 => centro
        #alineacion 2 => derecha
        alineacion = [1,1,1,0,0,0,2,1,2,2,2,2]
        vista = Vista.Vista(self, 'Busqueda de Articulos')
        vista.cargar_celdas(datos, columnas, alineacion)
        vista.ShowModal()

    def OnIncluirArticulosCeros(self, event):
        if self.checkBox1.GetValue():
            self.incluirCeros = ""
        else:
            self.incluirCeros = "AND (c.stock <> '0' OR d.stock <> '0' OR e.stock <> '0') "

    def OnButtonSalir(self, event):
        self.Close()
        
